USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_GetByCategory]    Script Date: 05/25/2011 06:54:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [classified].[ClassifiedAds_GetByCategory]
	@ResourceID bigint,
	@Categories categories readonly
AS
BEGIN

	SET NOCOUNT ON;
	
	SELECT 
      [ResourceID]
      ,AdID
      ,[CategoryID]
      ,[PostTime]
      ,[ExpiryTime]
      ,PostTime
      ,[Searching]
      ,[Title]
      ,[AdText]
      ,[TextFormat]
      ,CAST(Location.Lat as float) as latitude
      ,CAST(Location.Long as float) as longitude
      ,[PriceType]
      ,[Price]
      ,[ContactPhone]
      ,[ContactEmail]
      ,[ContactUrl]
      ,[UserID]
      ,[UserComputerID]
      ,[UserIP]
	FROM classified.ClassifiedAds
	WHERE ResourceID = @ResourceID AND CategoryID IN (SELECT CategoryID FROM @Categories) 
	AND ExpiryTime> GETDATE()
	ORDER BY classified.ClassifiedAds.PostTime
	
	
END






GO


